#!/usr/bin/python
# coding=utf8
from sqlalchemy.engine import create_engine
from config import DATABASE_URL
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm.session import sessionmaker
from database import MainRecordTable, ClientTable, AddressTable,\
    ModelTable, WarrantyTable, DisignTable, SerialNumberTable,\
    InventoryNumberTable, CodeTable, TTNTable, TTNListTable, SchetTable,\
    SchetListTable, CommentTable
from sqlalchemy.orm.exc import NoResultFound


table_list = {
    "Client":           ClientTable,
    "Address":          AddressTable,
    "Model":            ModelTable,
    "Warranty":         WarrantyTable,
    "Disign":           DisignTable,
    "SerialNumber":     SerialNumberTable,
    "InventoryNumber":  InventoryNumberTable,
    "Code":             CodeTable,
    "TTN":              TTNTable,
    "TTNList":          TTNListTable,
    "Schet":            SchetTable,
    "SchetList":        SchetListTable,
    "Comment":          CommentTable
}

table_search_column = {
    "Client":           'name',
    "Address":          'address',
    "Model":            'name',
    "Warranty":         'periud',
    "Disign":           'name',
    "SerialNumber":     'serial_number',
    "InventoryNumber":  'inventory_number',
    "Code":             'code',
    "TTN":              'ttn',
    "TTNList":          'ttn_id',
    "Schet":            'schet',
    "SchetList":        'schet_id',
    "Comment":          'comment'
}


def check_value(table, value, session):
    print "check value,", table, value
    try:
        param = getattr(table_list[table], table_search_column[table])
        obj = session.query(table_list[table]) \
                 .filter(param == value) \
                 .one()
    except NoResultFound:
        obj = table_list[table](value)
        session.add(obj)
        session.flush()
    return obj

engine = create_engine(DATABASE_URL, echo=False)
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
while True:
    q = session.query(MainRecordTable)\
        .filter(MainRecordTable.comment1 != None)\
        .filter(MainRecordTable.comment_id == 1)\
        .limit(1000)\
        .all()
    print "query length:", len(q)
    if not q: break
    for i in q:
        print i
        acomment = u"Нет комментария"
        session.query(TTNListTable).filter(TTNListTable.core_id == i.id).delete()
        session.query(SchetListTable).filter(SchetListTable.core_id == i.id).delete()
        if i.comment1:
            acomment = u"Комментарий1: " + i.comment1
            r = i.comment1.split('/')
            if len(r) == 2:
                attn = [r[0]]
                aschet = [r[1]]
        if i.comment2:
            acomment += u"; Комментарий2: " + i.comment2
            acode = i.comment2
        if i.comment3:
            acomment += u"; Комментарий3: " + i.comment3
        i.comment = check_value('Comment', acomment, session)
        if attn:
            try:
                ttn = [TTNListTable(check_value('TTN', int(j), session)) for j in attn]
                schet = [SchetListTable(check_value('Schet', int(j), session)) for j in aschet]
                i.ttn_list = ttn
                i.schet_list = schet
            except ValueError:
                print "ValueError"
                session.query(TTNListTable).filter(TTNListTable.core_id == 0).delete()
        if acode:
            i.code = check_value('Code', acode, session)
    print "COMMIT!!!"
    session.commit()
